import { randomUUID } from 'crypto';

/**
 * 将 camelCase 转换为 snake_case
 * @param {string} str - 要转换的字符串
 * @returns {string} 转换后的字符串
 */
function camelToSnakeCase(str) {
  return str.replace(/[A-Z]/g, letter => `_${letter.toLowerCase()}`);
}

/**
 * 将 snake_case 转换为 camelCase
 * @param {string} str - 要转换的字符串
 * @returns {string} 转换后的字符串
 */
function snakeToCamelCase(str) {
  return str.replace(/_([a-z])/g, (_, letter) => letter.toUpperCase());
}

/**
 * 将对象键从 snake_case 转换为 camelCase
 * @param {object} obj - 要转换的对象
 * @returns {object} 转换后的对象
 */
function convertObjectKeysToCamelCase(obj) {
  if (!obj || typeof obj !== 'object') return obj;
  return Object.keys(obj).reduce((result, key) => {
    result[snakeToCamelCase(key)] = obj[key];
    return result;
  }, {});
}

/**
 * 数据库操作基类，类似 MyBatis Plus，自动生成 SQL，并支持列名到 camelCase 转换
 */
export default class BaseMapper {
  /**
   * 构造函数
   * @param {object} db - SQLite3 数据库连接对象
   * @param {string} tableName - 表名
   * @param {string} [idField='id'] - 主键字段名
   * @param {boolean} [convertToSnakeCase=true] - 是否将属性名转换为 snake_case
   * @param {boolean} [convertToCamelCase=true] - 是否将查询结果的列名转换为 camelCase
   */
  constructor(db, tableName, idField = 'id', convertToSnakeCase = true, convertToCamelCase = true) {
    this.db = db;
    this.tableName = tableName;
    this.idField = idField;
    this.convertToSnakeCase = convertToSnakeCase;
    this.convertToCamelCase = convertToCamelCase;
  }

  /**
   * 获取转换后的列名
   * @param {string} key - 属性名
   * @returns {string} 转换后的列名
   */
  getColumnName(key) {
    return this.convertToSnakeCase ? camelToSnakeCase(key) : key;
  }

  /**
   * 插入数据
   * @param {object} entity - 要插入的数据对象
   * @returns {Promise<{lastID: number, changes: number}>} 插入结果
   */
  save(entity) {
    const keys = Object.keys(entity);
    const columnNames = keys.map(key => this.getColumnName(key));
    const values = keys.map(k => entity[k]);
    const placeholders = keys.map(() => '?').join(',');

    const sql = `INSERT INTO ${this.tableName} (${columnNames.join(',')}) VALUES (${placeholders})`;
    console.log('Executing SQL:', sql, 'with values:', values);

    return new Promise((resolve, reject) => {
      this.db.run(sql, values, function (err) {
        if (err) reject(err);
        else resolve({ lastID: this.lastID, changes: this.changes });
      });
    });
  }

  /**
   * 根据 ID 更新数据
   * @param {object} entity - 要更新的数据对象，包含 ID
   * @returns {Promise<{changes: number}>} 更新结果
   */
  updateById(entity) {
    // 过滤掉主键字段，确保不包含在 SET 语句中
    const keys = Object.keys(entity).filter(k => k !== this.idField && k !== snakeToCamelCase(this.getColumnName(this.idField)));
    if (keys.length === 0) {
      return Promise.reject(new Error('No fields to update'));
    }
    const columnNames = keys.map(key => this.getColumnName(key));
    const values = keys.map(k => entity[k]);
    const setStr = columnNames.map(col => `${col} = ?`).join(',');
    const idColumn = this.getColumnName(this.idField);

    // 尝试从 entity 中获取主键值，支持 camelCase 和 snake_case
    const idValue = entity[this.idField] ?? entity[snakeToCamelCase(this.getColumnName(this.idField))];
    if (idValue === undefined) {
      return Promise.reject(new Error(`Primary key '${this.idField}' or its camelCase/snake_case equivalent not found in entity`));
    }

    const sql = `UPDATE ${this.tableName} SET ${setStr} WHERE ${idColumn} = ?`;
    console.log('Executing SQL:', sql, 'with values:', [...values, idValue]);

    return new Promise((resolve, reject) => {
      this.db.run(sql, [...values, idValue], function (err) {
        if (err) reject(err);
        else resolve({ changes: this.changes });
      });
    });
  }

  /**
   * 根据 ID 删除数据
   * @param {string|number} id - 要删除的记录 ID
   * @returns {Promise<{changes: number}>} 删除结果
   */
  removeById(id) {
    const idColumn = this.getColumnName(this.idField);
    const sql = `DELETE FROM ${this.tableName} WHERE ${idColumn} = ?`;
    console.log('Executing SQL:', sql, 'with values:', [id]);

    return new Promise((resolve, reject) => {
      this.db.run(sql, [id], function (err) {
        if (err) reject(err);
        else resolve({ changes: this.changes });
      });
    });
  }

  /**
   * 根据 ID 查询数据
   * @param {string|number} id - 要查询的记录 ID
   * @returns {Promise<object>} 查询结果
   */
  getById(id) {
    const idColumn = this.getColumnName(this.idField);
    const sql = `SELECT * FROM ${this.tableName} WHERE ${idColumn} = ?`;
    console.log('Executing SQL:', sql, 'with values:', [id]);

    return new Promise((resolve, reject) => {
      this.db.get(sql, [id], (err, row) => {
        if (err) reject(err);
        else resolve(this.convertToCamelCase ? convertObjectKeysToCamelCase(row) : row);
      });
    });
  }

  /**
   * 根据条件查询单条数据
   * @param {object} [condition={}] - 查询条件
   * @returns {Promise<object>} 查询结果
   */
  getOne(condition = {}) {
    let sql = `SELECT * FROM ${this.tableName}`;
    const keys = Object.keys(condition);
    const values = keys.map(k => condition[k]);
    const columnNames = keys.map(key => this.getColumnName(key));

    if (keys.length) {
      const whereStr = columnNames.map(col => `${col} = ?`).join(' AND ');
      sql += ` WHERE ${whereStr}`;
    }

    console.log('Executing SQL:', sql, 'with values:', values);

    return new Promise((resolve, reject) => {
      this.db.get(sql, values, (err, row) => {
        if (err) reject(err);
        else if (!row) resolve(null);
        else resolve(this.convertToCamelCase ? convertObjectKeysToCamelCase(row) : row);
      });
    });
  }

  /**
   * 查询列表（支持分页和排序）
   * @param {object} [condition={}] - 查询条件
   * @param {number} [page] - 页码（从 1 开始），不传则返回所有数据
   * @param {number} [pageSize] - 每页条数，不传则返回所有数据
   * @param {string} [orderBy] - 排序条件，格式如 "columnName ASC" 或 "columnName DESC"
   * @returns {Promise<{list: object[], total: number}>} 查询结果和总数
   */
  list(condition = {}, page, pageSize, orderBy) {
    let sql = `SELECT * FROM ${this.tableName}`;
    let countSql = `SELECT COUNT(*) as total FROM ${this.tableName}`;
    const keys = Object.keys(condition);
    const values = keys.map(k => condition[k]);
    const columnNames = keys.map(key => this.getColumnName(key));
    const params = [];

    // 处理查询条件
    if (keys.length) {
      const whereStr = columnNames.map(col => `${col} = ?`).join(' AND ');
      sql += ` WHERE ${whereStr}`;
      countSql += ` WHERE ${whereStr}`;
      params.push(...values);
    }

    // 处理排序
    if (orderBy) {
      const [column, direction = 'ASC'] = orderBy.split(' ').filter(Boolean);
      const safeColumn = this.getColumnName(column);
      const safeDirection = direction.toUpperCase() === 'DESC' ? 'DESC' : 'ASC';
      sql += ` ORDER BY ${safeColumn} ${safeDirection}`;
    }

    // 处理分页
    if (page && pageSize) {
      const offset = (page - 1) * pageSize;
      sql += ` LIMIT ? OFFSET ?`;
      params.push(pageSize, offset);
    }

    console.log('Executing count SQL:', countSql, 'with values:', values);
    console.log('Executing SQL:', sql, 'with values:', params);

    return new Promise((resolve, reject) => {
      this.db.get(countSql, values, (err, countRow) => {
        if (err) return reject(err);

        this.db.all(sql, params, (err, rows) => {
          if (err) return reject(err);
          const convertedRows = this.convertToCamelCase
            ? rows.map(row => convertObjectKeysToCamelCase(row))
            : rows;
          resolve({
            list: convertedRows,
            total: countRow.total
          });
        });
      });
    });
  }
}
